CREATE DATABASE [NewTech_BookStore]
GO

USE [NewTech_BookStore]
GO

CREATE TABLE [dbo].[Book](
	[BookID] [char](5) NOT NULL,
	[Title] [nvarchar](200) NOT NULL,
	[CategoryName] [nvarchar](100) NOT NULL,
	[Author] [nvarchar](50) NOT NULL,
	[Publisher] [nvarchar](50) NULL,
	[ISBN] [char](10) NOT NULL,
	[Price] [money] NOT NULL,
	[Description] [varchar](200) NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
	[BookID] ASC
))
GO

CREATE TABLE [dbo].[Users](
	[UserName] [varchar](10) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[Address] [varchar](50) NOT NULL,
	[State] [varchar](50) NULL,
	[City] [varchar](50) NOT NULL,
	[Phone] [varchar](30) NULL,
	[Email] [varchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserName] ASC
))
GO

CREATE TABLE [dbo].[PaymentDetails](
	[PaymentID] [char](5) NOT NULL,
	[UserName] [varchar](10) NULL,
	[CreditCardType] [varchar](20) NULL,
	[CreditCardNumber] [varchar](16) NULL,
	[TotalBillAmount] [money] NULL,
	[PurchaseDate] [datetime] NULL,
 CONSTRAINT [PK_PaymentDetails] PRIMARY KEY CLUSTERED 
(
	[PaymentID] ASC
))
GO

ALTER TABLE [dbo].[PaymentDetails]  WITH CHECK ADD FOREIGN KEY([UserName])
REFERENCES [dbo].[Users] ([UserName])
GO

CREATE TABLE [dbo].[BillDetails](
	[BillNumber] [char](5) NOT NULL,
	[BookID] [char](5) NULL,
	[Rate] [char](1) NULL,
	[PaymentID] [char](5) NULL,
 CONSTRAINT [PK_BillDetails] PRIMARY KEY CLUSTERED 
(
	[BillNumber] ASC
))
GO

ALTER TABLE [dbo].[BillDetails]  WITH CHECK ADD FOREIGN KEY([BookID])
REFERENCES [dbo].[Book] ([BookID])
GO

ALTER TABLE dbo.BillDetails  WITH CHECK ADD FOREIGN KEY(PaymentID)
REFERENCES dbo.PaymentDetails (PaymentID)
GO

CREATE VIEW [dbo].[v_payment] AS
SELECT T1.PaymentID,T1.UserName,T2.FirstName,T2.LastName,T1.CreditCardType,T1.CreditCardNumber,T1.PurchaseDate,T1.TotalBillAmount 
FROM dbo.PaymentDetails T1, dbo.Users T2 WHERE T1.UserName = T2.UserName
GO

INSERT dbo.Book VALUES  ('b0001','Icons of Mens Style','Design','Sims, Josh',' Laurence King Publishing','1856697223','18.97','Womenswear progresses in leaps and bounds, fuelled by the readiness of women to wear what may at the time be perceived as the radical or outrageous.')
INSERT dbo.Book VALUES  ('b0002','The Red Queen','Fiction','Gregory, Philippa','Simon & Schuster Ltd','1847394655','14.35','The second book in Philippas stunning new trilogy')
INSERT dbo.Book VALUES  ('b0003','LAN Wiring','Computer','Trulove, James','McGraw-Hill','0071459758','26.90','This completely updated edition of the best-selling guide to cable installation for voice and data provides installers')
INSERT dbo.Book VALUES  ('b0004','The Collected Works of F. Scott Fitzgerald','Fiction','Fitzgerald','Wordsworth Editions Ltd','1840226595','11.80','This title features collected works of F. Scott Fitzgerald')
INSERT dbo.Book VALUES  ('b0005','Learning Python','Computer','Lutz, Mark','Shroff Publishers & Distributors Pvt Ltd','8184048262','30.50','')
INSERT dbo.Book VALUES  ('b0006','Lladd Duw','Fiction','Prysor','Y Lolfa','111','9.95','')
INSERT dbo.Book VALUES  ('b0007','The Daughter Game','Fiction','Long Kate','Pan Macmillan','0330435469','15.99','Anna is in her early forties. She is a teacher, although she isn''t sure she wants to be any more, and a wife - she''s not sure about that, either.')
INSERT dbo.Book VALUES  ('b0008','Israel''s Divine Healer','Fiction','Brown Michael L','Zondervan','0310200296','12.99','')
INSERT dbo.Book VALUES  ('b0009','Terraces, Balconies, Roofgardens and Patios','Design','Loft Publications','Loft Publications','8492731885','28.00','Green spaces have gone from being just an aesthetic contribution to the landscape to a key element in sustainable and environmental development.outrageous.')
INSERT dbo.Book VALUES  ('b0010','A Guide to Preparing Your Portfolio','Design','Jay McCauley','A & C Black Publishers Ltd','1408114364','11.89','This comprehensive manual is the complete guide to how to compile and prepare your portfolio for jobs, commissions, presentations or art college and is aimed at all areas of the Visual Arts.')
INSERT dbo.Book VALUES  ('b0011','The Power and the Glory','Sport','Simon Wilde','Simon & Schuster Ltd','1847376487','14.00','Ian Botham arrived on the international scene just in time to ride sport''s first big financial wave and exploit the Thatcherite mantra of go-out-and-get-what-you-want.')
INSERT dbo.Book VALUES  ('b0012',' The Sports Management Toolkit','Sport','Paul Emery','Taylor & Francis Ltd','0415491592','24.99','The Sports Management Toolkit is a practical guide to the most important management tools and techniques available to those working in the sport and leisure industries.')
INSERT dbo.Book VALUES  ('b0013','Sporting Heroes of the North','Sport','Dave Russell','Northumbria University','1904794297','8.40','Sporting Heroes of the North looks at the remarkable sporting culture and traditions of the north as well as some of the region''s sporting greats.')
INSERT dbo.Book VALUES  ('b0014','PROLOG Programming for Artificial Intelligence','Computer','Ivan Bratko','Pearson Education','0321417461','49.99','The fourth edition of this best-selling guide to Prolog and Artificial Intelligence has been updated to include key developments in the field while retaining its lucid approach to these topics')
INSERT dbo.Book VALUES  ('b0015','Easy PC','Computer','Kenneth Mole','Constable and Robinson','0716021935','4.19','Switch on your computer and discover a whole new world')
INSERT dbo.Book VALUES  ('b0016','Symbolic C++','Computer','Kiat Shi Tan','Springer London Ltd','1852332603','48.99','Symbolic C++: An Introduction to Computer Algebra Using Object-Oriented Programming provides a concise introduction to C++ and object-oriented programming')
INSERT dbo.Book VALUES  ('b0017','C''est La Vie','Fiction','Bruce Hodson','Melrose Books','1907040684','6.99','C''est La Vie recalls the fictional account of some of the adventures experienced by gay couple Nigel and Adam who run a legal practice in the middle east of England.')
INSERT dbo.Book VALUES  ('b0018','The Art of Contraception','Fiction','Susie Wild','Parthian Books','1906998035','6.99','A talent for razor-sharp, satirical observation - Nigel Jenkins Susie Wild''s debut collection is a quirky mix in which tales of the fantastic')

INSERT dbo.Users VALUES ('Gusto','cfx3345','Gustavo','Achong','1970 Napa Ct.','Washington','Bothell','398-555-0132','Gustavo@gmail.com')
INSERT dbo.Users VALUES ('Maxi123','g23ghh8','Catherine','Abel','9228 Via Del Sol','Arizona','Phoenix','747-555-0171','Maxi123@gmail.com')
INSERT dbo.Users VALUES ('Pixar','123456','Pilarcc','Ackerman','34 Waterloo Road','Victoria','Melbourne','1 (11) 500 555-0132','pixxxx23@msn.com')
INSERT dbo.Users VALUES ('dona21','doooo123','Donald','Blanton','2472 Alexander Place','California','West Covina','357-555-0161','donald0@adventure-works.com')
INSERT dbo.Users VALUES ('Lina12','lxxlsss1','Linda','Burnett','1397 Paradise Ct.','Washington','Newport Hills','121-555-0121','linda4@adventure-works.com')
INSERT dbo.Users VALUES ('Mic321','12sss23','Michael','Blythe','3385 Crestview Drive','Washington','Everett','126-555-0172','michael11@adventure-works.com')
INSERT dbo.Users VALUES ('Gab000','g1111123','Gabriel','Bockenkamp','628 Muir Road','California','Los Angeles','763-555-0145','gabriel0@adventure-works.com')